package org.example;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CRUDUtils {
    private static final String INSERT_CARD = "INSERT INTO Cards (number, name, validity_date, cvc, type, picture) VALUES (?, ?, ?, ?, ?, ?);";
    private static final String UPDATE_CARD = "UPDATE cards SET number = ? WHERE id = ?;";
    private static final String DELETE_CARD = "DELETE FROM cards WHERE id = ?;";

    static Logger logger = Logger.getLogger("MyLog");
    static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy_MM_dd__hh_mm_ss");
    static String date = simpleDateFormat.format(new java.util.Date());
    static FileHandler fh;

    static {
        try {
            fh = new FileHandler("log_" + date + ".log", true);
            CRUDUtils.logger.addHandler(fh);
            CRUDUtils.logger.setUseParentHandlers(false);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Создание новой таблицы
     *
     * @param con соединение с базой данных
     * @param tableName имя создаваемой таблицы
     */
    public static void createTable(Connection con,String tableName) {
        Statement stmt;

        try {
            String query="create table if not exists " + tableName + " (id serial primary key,\n" +
                    "    number varchar default '0000 0000 0000 0000' not null,\n" +
                    "    name varchar default 'MOMENTUM' not null,\n" +
                    "    validity_date date not null default current_date,\n" +
                    "    cvc varchar default '000' not null,\n" +
                    "    type varchar default 'Mir' not null,\n" +
                    "    picture boolean);";
            stmt=con.createStatement();
            stmt.executeUpdate(query);
            CRUDUtils.logger.log(Level.INFO, "Table has been created successfully");
        }
        catch (Exception e) {
            CRUDUtils.logger.log(Level.WARNING, "Table create failed !!", e);
            e.printStackTrace();
        }
    }

    /**
     * Считывает Карты из базы данных на основе заданного запроса.
     *
     * @param query SQL-запрос для выполнения
     * @return список Карт
     */
    public static List<Card> readCards(String query) {
        List<Card> cards = new ArrayList<>();

        try(Connection connection = DBUtils.connectToDb();
            PreparedStatement preparedStatement = connection.prepareStatement(query);) {

            ResultSet rs = preparedStatement.executeQuery();

            while (rs.next()) {
                String number = rs.getString("number");
                String name = rs.getString("name");
                LocalDate validityDate = rs.getDate("validity_date").toLocalDate();
                String cvc = rs.getString("cvc");
                String type = rs.getString("type");
                Boolean picture  = rs.getBoolean("picture");
                cards.add(new Card(number, name, validityDate, cvc, type, picture));
            }
            CRUDUtils.logger.log(Level.INFO, "Read all cards to list");
        } catch (SQLException e) {
            CRUDUtils.logger.log(Level.WARNING, "Read failed !!", e);
            e.printStackTrace();
        }
        return cards;
    }

    /**
     * Добавление новой Карты в базу данных
     *
     * @param card объект Card для добавления
     */
    public static void addCard(Card card) {
        try(Connection connection = DBUtils.connectToDb();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_CARD);) {

            preparedStatement.setString(1, card.getNumber());
            preparedStatement.setString(2, card.getName());
            preparedStatement.setDate(3, Date.valueOf(card.getValidityDate()));
            preparedStatement.setString(4, card.getCvc());
            preparedStatement.setString(5, card.getType());
            preparedStatement.setBoolean(6, card.getIsPicture());
            preparedStatement.executeUpdate();

            CRUDUtils.logger.log(Level.INFO, "Add new card");
        } catch (SQLException e) {
            CRUDUtils.logger.log(Level.WARNING, "Add failed !!", e);
            e.printStackTrace();
        }
    }

    /**
     * Изменение номера Карте
     *
     * @param cardId id Карты, данные которой надо изменить
     * @param number новый номер Карты
     */

    public static void updateCard(int cardId, String number) {
        try(Connection connection = DBUtils.connectToDb();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_CARD);) {

            preparedStatement.setString(1, number);
            preparedStatement.setInt(2, cardId);
            preparedStatement.executeUpdate();

            CRUDUtils.logger.log(Level.INFO, "Update card");
        } catch (SQLException e) {
            CRUDUtils.logger.log(Level.WARNING, "Add failed !!", e);
            e.printStackTrace();
        }
    }

    /**
     * Удаление Карты
     *
     * @param cardId id удаляемой карты
     */
    public static void deleteCard(int cardId) {
        try(Connection connection = DBUtils.connectToDb();
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_CARD);) {

            preparedStatement.setInt(1, cardId);
            preparedStatement.executeUpdate();

            CRUDUtils.logger.log(Level.INFO, "Delete card: cardId = " + cardId);
        } catch (SQLException e) {
            CRUDUtils.logger.log(Level.WARNING, "Delete card: cardId = " + cardId + " failed !!", e);
            e.printStackTrace();
        }
    }
}
